var sql={
	name:"sql",
	promptStr:"Sql>",
	commandArray:[],
	commandArrayIndex:-1,
	isDataLoad:false
};

//初始化
sql.initCtx=function(){
	if(!sql.isDataLoad){
		//加载数据库文件
		$.get("./data/database.data",{},function(data){
			$(".systemData").append(data);
			sql.isDataLoad=true;
			},"html"); 
	}
};

//创建表
sql.createTable=function(tableName,colDefs){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length!=0){
        vShell.showInfo("表【"+tableName+"】已存在！");
    }else{
        $(".systemData database objects").append("<object type='table' name='"+tableName+"'><define></define><data></data></object>");
        for(var i=0;i<colDefs.length;i++){
            $(".systemData database object[type='table'][name='"+tableName+"'] define").append("<column name='"+colDefs[i].name+"' type='"+colDefs[i].type+"'/>");
        }
        vShell.showInfo("表【"+tableName+"】已创建！");
    }
};

//删除表
sql.dropTable=function(tableName){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length==0){
        vShell.showInfo("表【"+tableName+"】不存在！");
    }else{
        table.remove();
        vShell.showInfo("表【"+tableName+"】已删除！");
    }
};

//插入记录
sql.insertRow=function(tableName,cols,values){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length==0){
        vShell.showInfo("表【"+tableName+"】不存在！");
    }else{
        if(cols==""||cols==[]){
            cols=[];
            table.find("define column").each(function(i){
                cols.push($(this).attr("name"));
            });
        }
        var rowId=util.randomString();
        table.find("data").append("<row id='"+rowId+"' />");
        var newRow=table.find("data").find("row[id='"+rowId+"']");
        for(var i=0;i<cols.length;i++){
            newRow.attr(cols[i],values[i]);
        }
        vShell.showInfo("记录已插入！");
    }
};

//删除记录
sql.deleteRow=function(tableName,condition){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length==0){
        vShell.showInfo("表【"+tableName+"】不存在！");
    }else{
        if(typeof condition=='undefined'){
            table.find("data").find("row").remove();
        }else{
            var condition=condition.split("=");
            var colName=$.trim(condition[0]);
            var colVal=$.trim(condition[1]).replace(/['|"]/g,"");
            table.find("data").find("row["+colName+"='"+colVal+"']").remove();
        }
        vShell.showInfo("记录已删除！");
    }
};

//更新记录
sql.updateRow=function(tableName,updateCols,condition){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length==0){
        vShell.showInfo("表【"+tableName+"】不存在！");
    }else{
        updateCols=updateCols.replace(/['|"]/g,"");
        var updateColArray=updateCols.split(",");
        for(var i=0;i<updateColArray.length;i++){
            var updateInfo=updateColArray[i].split("=");
            var updateColName=$.trim(updateInfo[0]);
            var updateColVal=$.trim(updateInfo[1]);
            if(typeof condition=='undefined'){
                table.find("data").find("row").attr(updateColName,updateColVal);
            }else{
                var condition=condition.split("=");
                var colName=$.trim(condition[0]);
                var colVal=$.trim(condition[1]).replace(/['|"]/g,"");
                table.find("data").find("row["+colName+"='"+colVal+"']").attr(updateColName,updateColVal);
            }
        }
        vShell.showInfo("数据已更新！");
    }
};

//查询记录
sql.selectRow=function(tableName,selectCols,condition,orderBy,groupBy){
	var table=$(".systemData database object[type='table'][name='"+tableName+"']");
    if(table.length==0){
        vShell.showInfo("表【"+tableName+"】不存在！");
    }else{
        cols=[];
        if(selectCols=="*"){
            table.find("define column").each(function(i){
                cols.push($(this).attr("name"));
            });
        }else{
            cols=selectCols.split(/,|\s/);
        }
        var selectOutput="<table>";
         
        selectOutput+="<tr>";
        for(var i=0;i<cols.length;i++){
            selectOutput+="<th>"+cols[i]+"</th>";
        }
        selectOutput+="</tr>";
         
        var selectRowData=[];
        if(typeof condition=='undefined'){
            selectRowData=table.find("data").find("row");
        }else{
            var condition=condition.split("=");
            var colName=$.trim(condition[0]);
            var colVal=$.trim(condition[1]).replace(/['|"]/g,"");
            selectRowData=table.find("data").find("row["+colName+"='"+colVal+"']");
        }
        selectRowData.each(function(i){
            selectOutput+="<tr>";
            for(var i=0;i<cols.length;i++){
                selectOutput+="<td>"+$(this).attr(cols[i])+"</td>";
            }
            selectOutput+="</tr>";
        });
        selectOutput+="</table>";
        vShell.showInfo(selectOutput);
        vShell.showInfo("查询完毕！");
    }
};

//执行SQL命令
sql.executeSql=function(sqlStr){
	if(sqlStr!=""){
		var result=null;
		if((result=(/^\s*create\s+table\s+(\w+)\s*\(([\s|\w|,|\(|\)]+?)\)\s*$/i).exec(sqlStr))!=null){
			//create table语句
			var tableName=result[1];
            var colDefs=[];
            var colDefsArray=result[2].split(",");
            for(var i=0;i<colDefsArray.length;i++){
                var colDefArray=colDefsArray[i].split(" ");
                var colDef={};
                colDef.name=$.trim(colDefArray[0]);
                colDef.type=$.trim(colDefArray[1]);
                colDefs.push(colDef);
            }
            sql.createTable(tableName,colDefs);
		}else if((result=(/^\s*drop\s+table\s+(\w+)\s*$/i).exec(sqlStr))!=null){
			//drop table语句
			var tableName=result[1];
			sql.dropTable(tableName);
		}else if((result=(/^\s*select\s+([\w|\*|\s|,]+?)\s+from\s+(\w+)(?:\s+where\s+([\s*|\w|=|'|"]+))?\s*$/i).exec(sqlStr))!=null){
			//select语句
			var selectCols=result[1];
            var tableName=result[2];
            var condition=result[3];
            if(typeof condition=='undefined'||/^\s*(\w+)\s*=\s*(['|"]?\w+['|"]?)\s*$/.test(condition)){
                sql.selectRow(tableName,selectCols,condition);
            }else{
                vShell.showInfo("目前where条件支持有限，我已经尽力了，期待明天会更好吧！");
            }
		}else if((result=(/^\s*insert\s+into\s*(\w+)\s*(\([\s|\w|,]+?\))?\s+values\s*\(([\s|\w|,|'|"]+)\)\s*$/i).exec(sqlStr))!=null){
			//insert语句
			var tableName=result[1];
            var cols=(typeof result[2]!='undefined')?result[2].replace(/[\(|\)]/g,"").split(","):[];
            var values=result[3].replace(/['|"]/g,"").split(",");
            sql.insertRow(tableName,cols,values);
		}else if((result=(/^\s*update\s+(\w+)\s+set\s+([\s|\w|=|,|'|"]+?)(?:\s+where\s+([\s|\w|=|'|"]+))?\s*$/i).exec(sqlStr))!=null){
			//update语句
			var tableName=result[1];
            var updateCols=result[2];
            var condition=result[3];
            if(typeof condition=='undefined'||/^\s*(\w+)\s*=\s*(['|"]?\w+['|"]?)\s*$/.test(condition)){
                sql.updateRow(tableName,updateCols,condition);
            }else{
                vShell.showInfo("目前where条件支持有限，我已经尽力了，期待明天会更好吧！");
            }
		}else if((result=(/^\s*delete\s+from\s+(\w+)(?:\s+where\s+([\s|\w|=|'|"]+))?\s*$/i).exec(sqlStr))!=null){
			//delete语句
			var tableName=result[1];
            var condition=result[2];
            if(typeof condition=='undefined'||/^\s*(\w+)\s*=\s*(['|"]?\w+['|"]?)\s*$/.test(condition)){
                sql.deleteRow(tableName,condition);
            }else{
                vShell.showInfo("目前where条件支持有限，我已经尽力了，期待明天会更好吧！");
            }
		}else{
			vShell.showInfo("无效sql!");
		}
	}
};

//监听按键事件
sql.keyDownHandle=function(event){
	switch(event.keyCode){
		case 13:	//回车键
		{
			var input=vShell.getInput();
			if(this.commandArray.length==10) {this.commandArray.shift();}
			this.commandArrayIndex = this.commandArray.push(input);
			var command=input.replace(sql.promptStr,"");
			if("exit"!=command){
				if(command.substr(-1)==";"){
					vShell.showInfo(input);
					sql.executeSql(command.substring(0,command.length-1));
					vShell.setEditAndFocus(sql.promptStr);
					return false;
				}
			}else{
				vShell.showInfo(input);
				vShell.currCtx=null;
				vShell.setEditAndFocus(vShell.promptStr);
				return false;
			}
		}break;
		case 8: 	//删除键
		{
			if(vShell.getInput()==sql.promptStr){
				return false;
			}
		}break;
		case 38: 	//上键
		{
			if(this.commandArrayIndex>0){
				vShell.setEditAndFocus(this.commandArray[--this.commandArrayIndex]);
			}
            return false;
		}break;
		case 40: 	//下键
		{
			if(this.commandArrayIndex<this.commandArray.length-1){
				vShell.setEditAndFocus(this.commandArray[++this.commandArrayIndex]);
			}
            return false;
		}break;

	}
};